﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;

namespace HttpModuleAccessControl
{
    class Database
    {

        private static string DBConnectionString = Config.DBConnectionString;

        /// <summary>
        /// Get user properties and associated roles of a specific user
        /// </summary>
        /// <param name="UserID"></param>
        /// <returns></returns>
        public static User GetUser(string UserID)
        {
            User usr = new User();
            try
            {
                using (SqlConnection conn = new SqlConnection(DBConnectionString))
                {
                    conn.Open();

                    //load user properties
                    string _tsql = "select Password,Username,UserTitle,UserDescription,DirectorID from users where UserID=@UserID";
                    SqlCommand comm = new SqlCommand(_tsql, conn);
                    comm.Parameters.Add(new SqlParameter("@UserID", UserID));
                    SqlDataReader dr = comm.ExecuteReader();
                    if (dr.HasRows)
                    {
                        while (dr.Read())
                        {
                            usr.UserID = UserID;
                            usr.Password = Convert.IsDBNull(dr[1]) ? "" : dr[1].ToString().Trim();
                            usr.Name = Convert.IsDBNull(dr[2]) ? "" : dr[2].ToString().Trim();
                            usr.UserTitle = Convert.IsDBNull(dr[3]) ? "" : dr[3].ToString().Trim();
                            usr.UserDescription = Convert.IsDBNull(dr[4]) ? "" : dr[4].ToString().Trim();
                            usr.DirectorID = Convert.IsDBNull(dr[5]) ? "" : dr[5].ToString().Trim();
                        }
                    }
                    dr.Close();

                    //load role ID associated with the user
                    string _tsql2 = "select roleid from userinrole where userid=@UserID";
                    SqlCommand command2 = new SqlCommand(_tsql2, conn);
                    command2.Parameters.Add(new SqlParameter("@UserID", UserID));
                    SqlDataReader dr2 = command2.ExecuteReader();
                    if (dr2.HasRows)
                    {
                        while (dr2.Read())
                        {
                            usr.RoleList.Add(dr2[0].ToString().Trim());
                        }
                    }
                    dr2.Close();                       

                }
            }
            catch (Exception ex)
            {
                Log.Write("Error in Database.cs GetUser method, error=" + ex.Message);

            }

            return usr;
        }

        /// <summary>
        /// Get all users from database
        /// </summary>
        /// <returns></returns>
        public static Users GetUsers()
        {
            Users usrs = new Users();
            try
            {
                using (SqlConnection conn = new SqlConnection(DBConnectionString))
                {
                    conn.Open();
                    SqlCommand comm = new SqlCommand("select UserID,Password,Username,UserTitle,UserDescription,DirectorID from users", conn);
                    SqlDataReader dr = comm.ExecuteReader();
                    if (dr.HasRows)
                    {
                        while (dr.Read())
                        {
                            User usr = new User();
                            usr.UserID = Convert.IsDBNull(dr[0]) ? "" : dr[0].ToString().Trim ();
                            usr.Password = Convert.IsDBNull(dr[1]) ? "" : dr[1].ToString().Trim ();
                            usr.Name = Convert.IsDBNull(dr[2]) ? "" : dr[2].ToString().Trim ();
                            usr.UserTitle = Convert.IsDBNull(dr[3]) ? "" : dr[3].ToString().Trim();
                            usr.UserDescription = Convert.IsDBNull(dr[4]) ? "" : dr[4].ToString().Trim();
                            usr.DirectorID = Convert.IsDBNull(dr[5]) ? "" : dr[5].ToString().Trim();
                            usrs.Add(usr);
                        }
                    }
                    dr.Close();

                    // get role list for users
                    foreach (User u in usrs)
                    {
                        string _tsql2 = "select roleid from userinrole where userid=@UserID";
                        SqlCommand command2 = new SqlCommand(_tsql2, conn);
                        command2.Parameters.Add(new SqlParameter("@UserID", u.UserID ));
                        SqlDataReader dr2 = command2.ExecuteReader();
                        if (dr2.HasRows)
                        {
                            while (dr2.Read())
                            {
                                u.RoleList.Add(dr2[0].ToString().Trim());
                            }
                        }
                        dr2.Close(); 
                    }

                }
            }
            catch (Exception ex)
            {
                    Log.Write("Error in Database.cs GetUsers method, desc=" + ex.Message);

            }

            return usrs;

        }

        /// <summary>
        /// Get role properties for a specific role, including permission IDs associated with this role.
        /// </summary>
        /// <param name="RoleID"></param>
        /// <returns></returns>
        public static Role GetRole(int RoleID)
        {
            Role rle = new Role();
            try
            {
                using (SqlConnection conn = new SqlConnection(DBConnectionString))
                {
                    conn.Open();

                    //load role properties
                    string _tsql = "select rolename, parentid from roles where roleid=@RoleID";
                    SqlCommand comm = new SqlCommand(_tsql, conn);
                    comm.Parameters.Add(new SqlParameter("@RoleID", RoleID));
                    SqlDataReader dr = comm.ExecuteReader();
                    if (dr.HasRows)
                    {
                        while (dr.Read())
                        {
                            rle.RoleID = RoleID;
                            rle.RoleName = Convert.IsDBNull(dr[1]) ? string.Empty : dr[1].ToString().Trim();
                            rle.ParentID = Convert.IsDBNull(dr[2]) ? 0 : Convert.ToInt32(dr[2].ToString().Trim());
                        }
                    }
                    dr.Close();

                    //load permission id associated with the role
                    string _tsql2 = "select permissionid from dbo.RoleInPermission where roleid=@RoleID";
                    SqlCommand command2 = new SqlCommand(_tsql2, conn);
                    command2.Parameters.Add(new SqlParameter("@RoleID", RoleID));
                    SqlDataReader dr2 = command2.ExecuteReader();
                    if (dr2.HasRows)
                    {
                        while (dr2.Read())
                        {
                            rle.PermissionList.Add(dr2[0].ToString().Trim());
                        }
                    }
                    dr2.Close();

                }
            }
            catch (Exception ex)
            {
                Log.Write("Error in Database.cs GetRole(int RoleID) method, error=" + ex.Message);

            }

            return rle;
        }

        /// <summary>
        /// Get all roles from database
        /// </summary>
        /// <returns></returns>
        public static Roles GetRoles()
        {
            Roles rles = new Roles();

            try
            {
                using (SqlConnection conn = new SqlConnection(DBConnectionString))
                {
                    conn.Open();
                    SqlCommand comm = new SqlCommand("select RoleID,RoleName,ParentID from roles", conn);
                    SqlDataReader dr = comm.ExecuteReader();
                    if (dr.HasRows)
                    {
                        while (dr.Read())
                        {
                            Role rle = new Role();
                            rle.RoleID = Convert.IsDBNull(dr[0])? 0 : Convert.ToInt32(dr[0].ToString().Trim());
                            rle.RoleName = Convert.IsDBNull(dr[1])? string.Empty : dr[1].ToString().Trim() ;
                            rle.ParentID = Convert.IsDBNull(dr[2]) ? 0 : Convert.ToInt32(dr[2].ToString().Trim() );
                            
                            rles.Add(rle);                            
                        }
                    }
                    dr.Close();

                    foreach (Role rle2 in rles)
                    {
                        // get Permission ID for the role
                        string _tsql2 = "select permissionid from dbo.RoleInPermission where roleid=@RoleID";
                        SqlCommand command2 = new SqlCommand(_tsql2, conn);
                        command2.Parameters.Add(new SqlParameter("@RoleID", rle2.RoleID));
                        SqlDataReader dr2 = command2.ExecuteReader();
                        if (dr2.HasRows)
                        {
                            while (dr2.Read())
                            {
                                rle2.PermissionList.Add(dr2[0].ToString().Trim());
                            }
                        }
                        dr2.Close();
                    }

                }
            }
            catch (Exception ex)
            {
                Log.Write("Database.cs GetRoles method, error=" + ex.Message);

            }

            return rles;
        }

        /// <summary>
        /// Get all permissions from database
        /// </summary>
        /// <returns></returns>
        public static Permissions GetPermissions()
        {
            Permissions pers = new Permissions();

            try
            {
                using (SqlConnection conn = new SqlConnection(DBConnectionString))
                {
                    conn.Open();
                    SqlCommand comm = new SqlCommand("select permissionID,permissionName,ParentID,WebPageName,Writable from permissions", conn);
                    SqlDataReader dr = comm.ExecuteReader();
                    if (dr.HasRows)
                    {
                        while (dr.Read())
                        {
                            Permission per = new Permission();
                            per.PermissionID = Convert.IsDBNull(dr[0]) ? 0 : Convert.ToInt32(dr[0].ToString().Trim());
                            per.PermissionName = Convert.IsDBNull(dr[1]) ? "" : dr[1].ToString().Trim ();
                            per.ParentID = Convert.IsDBNull(dr[2]) ? 0 : Convert.ToInt32(dr[2].ToString().Trim ());
                            per.WebPageName = Convert.IsDBNull(dr[3]) ? "" : dr[3].ToString().Trim ();
                            per.Writable = Convert.IsDBNull(dr[4]) ? false : Convert.ToBoolean(dr[4].ToString().Trim ());
                            pers.Add(per);
                        }
                    }
                    dr.Close();

                }
            }
            catch (Exception ex)
            {
                Log.Write("Error in Database.cs GetPermissions method, error=" + ex.Message);

            }

            return pers;
        }

        /// <summary>
        /// Get Permission List by Role ID
        /// </summary>
        /// <param name="RoleID"></param>
        /// <returns></returns>
        public static Permissions GetPermissions(int RoleID)
        {
            Permissions pers = new Permissions();

            try
            {
                using (SqlConnection conn = new SqlConnection(DBConnectionString))
                {
                    conn.Open();
                    string _commandText = "select t1.permissionID,t1.permissionName,t1.ParentID,t1.WebPageName,t1.Writable " +
                                          " from permissions as t1 join RoleinPermission as t2 " +
                                          " on t1.permissionID = t2.permissionID " +
                                          " where t2.RoleID = @RoleID ";
                    SqlCommand comm = new SqlCommand(_commandText, conn);
                    comm.Parameters.Add(new SqlParameter("@RoleID", RoleID.ToString()));
                    SqlDataReader dr = comm.ExecuteReader();
                    if (dr.HasRows)
                    {
                        while (dr.Read())
                        {
                            Permission per = new Permission();
                            per.PermissionID = Convert.IsDBNull(dr[0]) ? 0 : Convert.ToInt32(dr[0].ToString().Trim ());
                            per.PermissionName = Convert.IsDBNull(dr[1]) ? "" : dr[1].ToString().Trim ();
                            per.ParentID = Convert.IsDBNull(dr[2]) ? 0 : Convert.ToInt32(dr[2].ToString().Trim ());
                            per.WebPageName = Convert.IsDBNull(dr[3]) ? "" : dr[3].ToString().Trim ();
                            per.Writable = Convert.IsDBNull(dr[4]) ? false : Convert.ToBoolean(dr[4].ToString().Trim ());
                            pers.Add(per);
                        }
                    }
                    dr.Close();

                }
            }
            catch (Exception ex)
            {
                Log.Write("Error in Database.cs GetPermissions method, desc=" + ex.Message);

            }

            return pers;
        }



        public static void ClearQueryNotificationData()
        {
            //string _DBname = Config.DatabaseName;
            //if (_DBname = string.Empty)
            //{
            //    Log.Write("Database.cs, ClearQueryNotificationData method; Initial Catalog (DB name) is empty!");
            //    throw new Exception("Initial Catalog (DB name) is empty!");
            //}

            // future : change a nice way to clear the records in sys.dm_qn_subscriptions and queue
            // stored procedure is good but with some minor porblems.
            try
            {
                using (SqlConnection conn = new SqlConnection(DBConnectionString))
                {
                    conn.Open();
                    SqlCommand command1 = new SqlCommand();
                    command1.Connection = conn;
                    command1.CommandText = "kill query notification subscription ALL;";
                    command1.ExecuteNonQuery();

                    while (true)
                    {
                        SqlCommand command2 = new SqlCommand();
                        command2.Connection = conn;
                        command2.CommandText = "select top 1 * from " + Config.QueueName();
                        SqlDataReader reader = command2.ExecuteReader();
                        if (reader.HasRows)
                        {
                            // delete records
                            SqlCommand command3 = new SqlCommand();
                            command3.Connection = conn;
                            command3.CommandText = "receive * from " + Config.QueueName();
                            command3.ExecuteNonQuery();
                            reader.Close();
                        }
                        else
                        {
                            reader.Close();
                            break;
                        }
                    }



                }
            }
            catch (Exception ex)
            {
                Log.Write("Database.cs, ClearQueryNotificationData method; Exception= " + ex.Message);

            }



        }
        
    }

}
